package com.lukk.TEST;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import org.junit.Test;
import com.lukk.pojo.Province;
import com.lukk.pojo.Territory;
import com.lukk.util.DateUtils;
import com.lukk.util.HttpRequest;

/**
 * 
 * SqlTest 下午6:29:37 Copyright zhaocj Inc. All rights reserved. Love Me Like
 * Love Justin Bieber
 * 
 */
public class SqlTest {

	DBHelp db = new DBHelp();

	private static ExecutorService executorService = new ThreadPoolExecutor(28, 35, 0L, TimeUnit.MILLISECONDS,
			new LinkedBlockingQueue<Runnable>(100000), new ThreadPoolExecutor.CallerRunsPolicy());
	private static Random random = new Random();

	@Test
	public void test1() throws Exception {
		// 查询所有的省份
		String queryProvinces = "select id,name,pycode from lukk_province";
		Connection queryProvincesCon = db.getCon();
		PreparedStatement statement = queryProvincesCon.prepareStatement(queryProvinces);
		ResultSet executeQuery = statement.executeQuery();
		List<Province> allProvince = new ArrayList<Province>();
		while (executeQuery.next()) {
			Province province = new Province();
			province.setId(executeQuery.getInt("id"));
			province.setName(executeQuery.getString("name"));
			allProvince.add(province);
		}
		db.closeAll(queryProvincesCon, statement, executeQuery);
		System.out.println(allProvince.size());
		final String sql = "INSERT INTO `worker` VALUES (?, ?, '8477851e93e4a154f6a6153fd80102ab', ?, '1490325244_09336475_1.png', '0.00', '0.00',?, '0', '0', ?, '无', '无', null, 'f8a16b6a-3955-43b7-b186-6faa80a9d4b6', ?);";
		long start = System.currentTimeMillis();
		for (int i = 2000; i <= 1000000; i++) {
			final int num = i;
			executorService.submit(new Runnable() {
				@Override
				public void run() {
					Connection con = null;
					PreparedStatement pstm = null;
					try {
						con = db.getCon();
						pstm = con.prepareStatement(sql);
						// 设置ID
						pstm.setInt(1, num);
						// 设置手机号
						int randomNumber = getRandomNumber(100000, 200000);
						int randomNumber2 = getRandomNumber(10000, 99999);
						pstm.setString(2, String.valueOf(randomNumber) + String.valueOf(randomNumber2));
						pstm.setString(3, allProvince.get(getRandomNumber(0, 33)).getName());
						pstm.setString(4, getRondomName());
						pstm.setString(5, DateUtils.getDate("yyyy/MM/dd HH:mm:ss"));
						pstm.setInt(6, getRandomNumber(10, 90));
						pstm.execute();
						db.closeAll(con, pstm, null);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			});
		}
		executorService.shutdown(); // 该方法在加入线程队列的线程执行完之前不会执行。
		while (true) {
			if (executorService.isTerminated()) {
				System.out.println("所有的子线程都结束了！");
				long end = System.currentTimeMillis();
				System.out.println("执行时长： " + (end - start));
				break;
			}
		}
	}

	// 为工人添加工作区域
	@Test
	public void test2() throws Exception {
		String querysql = "select name as country	from chinaArea	where level =3 or level=4";
		Connection queryCon = db.getCon();
		PreparedStatement statement = queryCon.prepareStatement(querysql);
		ResultSet executeQuery = statement.executeQuery();
		List<Territory> allTerritory = new ArrayList<Territory>();
		while (executeQuery.next()) {
			Territory terr = new Territory();
			terr.setCountry(executeQuery.getString("country"));
			allTerritory.add(terr);
		}
		System.out.println(allTerritory.size() + "      全部城镇的大小");
		final String sql = "INSERT INTO `worker_workerSpace` VALUES (?,?);";
		long start = System.currentTimeMillis();
		for (int i = 2000; i <= 100000; i++) {
			executorService.submit(new Runnable() {
				@Override
				public void run() {
					Connection con = null;
					PreparedStatement pstm = null;
					try {
						con = db.getCon();
						pstm = con.prepareStatement(sql);
						// 设置手机号
						int randomNumber = getRandomNumber(2000, 4000);
						pstm.setInt(1, randomNumber);
						pstm.setString(2, allTerritory.get(getRandomNumber(0, allTerritory.size() - 1)).getCountry());
						pstm.execute();
						db.closeAll(con, pstm, null);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			});
		}
		executorService.shutdown(); // 该方法在加入线程队列的线程执行完之前不会执行。
		while (true) {
			if (executorService.isTerminated()) {
				System.out.println("所有的子线程都结束了！");
				long end = System.currentTimeMillis();
				System.out.println("执行时长： " + (end - start));
				break;
			}
		}
	}

	// 为工人添加工作区域
	@Test
	public void test3() throws Exception {
		// 河南 河北 云南 辽宁21
		// 筛选出来 最高省份
		String querysql = "select code,name as city from chinaArea where parentId = 42";
		Connection queryCon = db.getCon();
		PreparedStatement statement = queryCon.prepareStatement(querysql);
		ResultSet executeQuery = statement.executeQuery();
		while (executeQuery.next()) {
			String insertSQL = "insert into territory(province,city) values(?,?)";
			PreparedStatement prepareStatement3 = queryCon.prepareStatement(insertSQL);
			prepareStatement3.setString(1, "湖北省");
			prepareStatement3.setString(2, executeQuery.getString("city"));
			prepareStatement3.executeUpdate();
		}
		db.closeAll(queryCon, statement, executeQuery);
	}

	// 查询错误
	@Test
	public void test4() throws Exception {
		// 筛选出来 最高省份
		String querysql = "select code,name as province from chinaArea	where level = 1";
		Connection queryCon = db.getCon();
		PreparedStatement statement = queryCon.prepareStatement(querysql);
		ResultSet executeQuery = statement.executeQuery();
		List<Territory> allTerritory = new ArrayList<Territory>();
		while (executeQuery.next()) {
			Territory terr = new Territory();
			terr.setProvince(executeQuery.getString("province"));
			terr.setCode(executeQuery.getString("code"));
			allTerritory.add(terr);
		}
		System.out.println(allTerritory.size() + "      全部省份");
		for (Territory territory : allTerritory) {
			System.out.println("==============" + territory.getCode());
			// 根据省份得到所有的城市
			String querysql1 = "select code,name as city from chinaArea where parentID = '" + territory.getCode() + "'";
			System.out.println(querysql1);
			PreparedStatement prepareStatement2 = queryCon.prepareStatement(querysql1);
			ResultSet executeQuery2 = prepareStatement2.executeQuery();
			while (executeQuery2.next()) {
				System.out.println("====222222222222222222222222222====");
				Territory terr = new Territory();
				terr.setCity(executeQuery2.getString("city"));
				String insertSQL = "insert into territory(province,city) values(?,?)";
				PreparedStatement prepareStatement3 = queryCon.prepareStatement(insertSQL);
				prepareStatement3.setString(1, territory.getProvince());
				prepareStatement3.setString(2, terr.getCity());
				prepareStatement3.executeUpdate();
			}
		}
		queryCon.commit();
		db.closeAll(queryCon, statement, executeQuery);
	}

	public static int getRandomNumber(int min, int max) {
		int s = random.nextInt(max) % (max - min + 1) + min;
		return s;
	}

	// 查询全部的workerSpace
	public static String getRondomName() {
		String[] firsname = { "赵", "钱", "孙", "李", "周", "吴", "郑", "王", "冯", "陈", "楮", "卫", "蒋", "沈", "韩", "杨", "朱", "秦",
				"尤", "许", "何", "吕", "施", "张", "孔", "曹", "严", "华", "金", "魏", "陶", "姜", "戚", "谢", "邹", "喻", "柏", "水", "窦",
				"章", "云", "苏", "潘", "葛", "奚", "范", "彭", "郎", "鲁", "韦", "昌", "马", "苗", "凤", "花", "方", "俞", "任", "袁", "柳",
				"酆", "鲍", "史", "唐", "费", "廉", "岑", "薛", "雷", "贺", "倪", "汤", "滕", "殷", "罗", "毕", "郝", "邬", "安", "常", "乐",
				"于", "时", "傅", "皮", "卞", "齐", "康", "伍", "余", "元", "卜", "顾", "孟", "平", "黄", "和", "穆", "萧", "尹", "姚", "邵",
				"湛", "汪", "祁", "毛", "禹", "狄", "米", "贝", "明", "臧", "计", "伏", "成", "戴", "谈", "宋", "茅", "庞", "熊", "纪", "舒",
				"屈", "项", "祝", "董", "梁", "杜", "阮", "蓝", "闽", "席", "季", "麻", "强", "贾", "路", "娄", "危", "江", "童", "颜", "郭",
				"梅", "盛", "林", "刁", "锺", "徐", "丘", "骆", "高", "夏", "蔡", "田", "樊", "胡", "凌", "霍", "虞", "万", "支", "柯", "昝",
				"管", "卢", "莫", "经", "房", "裘", "缪", "干", "解", "应", "宗", "丁", "宣", "贲", "邓", "郁", "单", "杭", "洪", "包", "诸",
				"左", "石", "崔", "吉", "钮", "龚", "程", "嵇", "邢", "滑", "裴", "陆", "荣", "翁", "荀", "羊", "於", "惠", "甄", "麹", "家",
				"封", "芮", "羿", "储", "靳", "汲", "邴", "糜", "松", "井", "段", "富", "巫", "乌", "焦", "巴", "弓", "牧", "隗", "山", "谷",
				"车", "侯", "宓", "蓬", "全", "郗", "班", "仰", "秋", "仲", "伊", "宫", "宁", "仇", "栾", "暴", "甘", "斜", "厉", "戎", "祖",
				"武", "符", "刘", "景", "詹", "束", "龙", "叶", "幸", "司", "韶", "郜", "黎", "蓟", "薄", "印", "宿", "白", "怀", "蒲", "邰",
				"从", "鄂", "索", "咸", "籍", "赖", "卓", "蔺", "屠", "蒙", "池", "乔", "阴", "郁", "胥", "能", "苍", "双", "闻", "莘", "党",
				"翟", "谭", "贡", "劳", "逄", "姬", "申", "扶", "堵", "冉", "宰", "郦", "雍", "郤", "璩", "桑", "桂", "濮", "牛", "寿", "通",
				"边", "扈", "燕", "冀", "郏", "浦", "尚", "农", "温", "别", "庄", "晏", "柴", "瞿", "阎", "充", "慕", "连", "茹", "习", "宦",
				"艾", "鱼", "容", "向", "古", "易", "慎", "戈", "廖", "庾", "终", "暨", "居", "衡", "步", "都", "耿", "满", "弘", "匡", "国",
				"文", "寇", "广", "禄", "阙", "东", "欧", "殳", "沃", "利", "蔚", "越", "夔", "隆", "师", "巩", "厍", "聂", "晁", "勾", "敖",
				"融", "冷", "訾", "辛", "阚", "那", "简", "饶", "空", "曾", "毋", "沙", "乜", "养", "鞠", "须", "丰", "巢", "关", "蒯", "相",
				"查", "后", "荆", "红", "游", "竺", "权", "逑", "盖", "益", "桓", "公", "万俟", "司马", "上官", "欧阳", "夏侯", "诸葛", "闻人",
				"东方", "赫连", "皇甫", "尉迟", "公羊", "澹台", "公冶", "宗政", "濮阳", "淳于", "单于", "太叔", "申屠", "公孙", "仲孙", "轩辕", "令狐",
				"锺离", "宇文", "长孙", "慕容", "鲜于", "闾丘", "司徒", "司空", "丌官", "司寇", "仉", "督", "子车", "颛孙", "端木", "巫马", "公西",
				"漆雕", "乐正", "壤驷", "公良", "拓拔", "夹谷", "宰父", "谷梁", "晋", "楚", "阎", "法", "汝", "鄢", "涂", "钦", "段干", "百里",
				"东郭", "南门", "呼延", "归", "海", "羊舌", "微生", "岳", "帅", "缑", "亢", "况", "后", "有", "琴", "梁丘", "左丘", "东门", "西门",
				"商", "牟", "佘", "佴", "伯", "赏", "南宫", "墨", "哈", "谯", "笪", "年", "爱", "阳", "佟" };
		String[] namelist = { "伟", "伟", "芳", "伟", "秀英", "秀英", "娜", "秀英", "伟", "敏", "静", "丽", "静", "丽", "强", "静", "敏",
				"敏", "磊", "军", "洋", "勇", "勇", "艳", "杰", "磊", "强", "军", "杰", "娟", "艳", "涛", "涛", "明", "艳", "超", "勇", "娟",
				"杰", "秀兰", "霞", "敏", "军", "丽", "强", "平", "刚", "杰", "桂英", "芳", "　嘉懿", "煜城", "懿轩", "烨伟", "苑博", "伟泽", "熠彤",
				"鸿煊", "博涛", "烨霖", "烨华", "煜祺", "智宸", "正豪", "昊然", "明杰", "立诚", "立轩", "立辉", "峻熙", "弘文", "熠彤", "鸿煊", "烨霖",
				"哲瀚", "鑫鹏", "致远", "俊驰", "雨泽", "烨磊", "晟睿", "天佑", "文昊", "修洁", "黎昕", "远航", "旭尧", "鸿涛", "伟祺", "荣轩", "越泽",
				"浩宇", "瑾瑜", "皓轩", "擎苍", "擎宇", "志泽", "睿渊", "楷瑞", "子轩", "弘文", "哲瀚", "雨泽", "鑫磊", "修杰", "伟诚", "建辉", "晋鹏",
				"天磊", "绍辉", "泽洋", "明轩", "健柏", "鹏煊", "昊强", "伟宸", "博超", "君浩", "子骞", "明辉", "鹏涛", "炎彬", "鹤轩", "越彬", "风华",
				"靖琪", "明诚", "高格", "光华", "国源", "冠宇", "晗昱", "涵润", "翰飞", "翰海", "昊乾", "浩博", "和安", "弘博", "宏恺", "鸿朗", "华奥",
				"华灿", "嘉慕", "坚秉", "建明", "金鑫", "锦程", "瑾瑜", "晋鹏", "经赋", "景同", "靖琪", "君昊", "俊明", "季同", "开济", "凯安", "康成",
				"乐语", "力勤", "良哲", "理群", "茂彦", "敏博", "明达", "朋义", "彭泽", "鹏举", "濮存", "溥心", "璞瑜", "浦泽", "奇邃", "祺祥", "荣轩",
				"锐达", "睿慈", "绍祺", "圣杰", "晟睿", "思源", "斯年", "泰宁", "天佑", "同巍", "奕伟", "祺温", "文虹", "向笛", "心远", "欣德", "新翰",
				"兴言", "星阑", "修为", "旭尧", "炫明", "学真", "雪风", "雅昶", "阳曦", "烨熠", "英韶", "永贞", "咏德", "宇寰", "雨泽", "玉韵", "越彬",
				"蕴和", "哲彦", "振海", "正志", "子晋", "自怡", "德赫", "君平" };
		int a = (int) Math.abs(firsname.length * Math.random());
		int b = (int) Math.abs(namelist.length * Math.random());
		String name = firsname[a] + namelist[b];
		return name;
	}

	@Test
	public void test5() throws Exception {
		String querysql = "select phone from worker where headImg is NULL";
		Connection queryCon = db.getCon();
		PreparedStatement statement = queryCon.prepareStatement(querysql);
		ResultSet executeQuery = statement.executeQuery();
		int i = 0;
		System.out.println(i + "  sdf  ");
		while (executeQuery.next()) {
			i++;
			System.out.println(i + "   数量");
			String phone = executeQuery.getString("phone");
			System.out.println(executeQuery.getString("phone"));
			HttpRequest.sendGet("http://v1.avatardata.cn/Sms/Send", "key=d4bc2afdcc6d4231bd8f793d9ca6d694&mobile="
					+ phone + "&templateId=52686fbe216b457890c2283740596dab&param=http://fir.im/ew6r");
		}
	}

	@Test
	public void test6() throws Exception {
		String sendGet = HttpRequest.sendGet("http://v1.avatardata.cn/Sms/Send",
				"key=d4bc2afdcc6d4231bd8f793d9ca6d694&mobile=" + "13233844503"
						+ "&templateId=52686fbe216b457890c2283740596dab&param=http://fir.im/ew6r");
		System.out.println(sendGet);
	}

	// 添加订单
	@Test
	public void test7() throws Exception {
		String insertSql = "INSERT INTO `indent` ( `indentPrice`, `beginTime`, `subscribeTime`, `acceptTime`, `workerId`, `type`, `status`, `remark`, `finishTime`, `afterSale`, `deposit`, `cuAdId`, `customerId`, `isDelete`, `orderId`, `isEva`, `couponsId`, `customerPrice`, `roomNumB`, `dealerId`, `quHAddress`, `weiXbody`, `indentType`, `addPrice`, `relievedBao`, `merchantsPrice`, `motorNum`, `bigouNum`, `sizhuagouNum`, `relievedPrice`) VALUES ('2.00', '2017-04-21 17:17:38', '2017-04-22 17:17:43', '2017-03-31 17:17:47', '2', ?, ?, '无', '2017-04-15 17:17:55', '0', '30.00', '11', '30', '30', '30', '0', '30', '30.00', '30', '30', '30', '无', '0', '0.00', '0', '0.00', '0', '0', NULL, NULL);";
		for (int i = 0; i <= 7000; i++) {
			executorService.submit(new Runnable() {
				@Override
				public void run() {
					Connection con = null;
					PreparedStatement pstm = null;
					try {
						con = db.getCon();
						pstm = con.prepareStatement(insertSql);
						pstm.setInt(1, getRandomNumber(0, 3));
						pstm.setInt(2, getRandomNumber(0, 8));
						// 设置手机号
						pstm.executeUpdate();
						db.closeAll(con, pstm, null);
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
			});
		}
		executorService.shutdown(); // 该方法在加入线程队列的线程执行完之前不会执行。
		while (true) {
			if (executorService.isTerminated()) {
				System.out.println("所有的子线程都结束了！");
				break;
			}
		}
	}

	@Test
	// lukk省份表
	public void test8() throws Exception {
		String selectSQL = "select * from chinaAreaTwo where level = 1";
		Connection queryCon = db.getCon();
		PreparedStatement statement = queryCon.prepareStatement(selectSQL);
		ResultSet executeQuery = statement.executeQuery();
		while (executeQuery.next()) {
			System.out.println(executeQuery.getString("code") + "  | |  " + executeQuery.getString("name"));
			String updateSql = "update lukk_province set areaCode = " + executeQuery.getString("code")
					+ "  where name = '" + executeQuery.getString("name")+"'";

			System.out.println(updateSql);
			statement = queryCon.prepareStatement(updateSql);
			statement.executeUpdate();
		}
	}
	@Test
	public void test9(){
		
		
		
	}
}
